/*
 * To change this template, choose Tools | Templates
 * and open the template in the editor.
 */
package Models;

import java.sql.*;
import java.util.Vector;

import Beans.NgonNgu;
import Beans.NhomMA;

/**
 *
 * @author Venus
 */
public class NhomMA_Model {

    public static int TaoMa() {
        Connection conn = null;
        ResultSet rss = null;
        Statement stmt = null;
        try {
            ConnectionPool pool = new ConnectionPool();
            conn = pool.getConnection();
            stmt = conn.createStatement();
            String query = "select max(MaNMA) from nhommonan";
            rss = stmt.executeQuery(query);
            int maht = 0;// biến maht chứa mã của khu vực hiện tại
            while (rss.next()) {
                maht = rss.getInt(1);
            }
            stmt.close();
            rss.close();
            conn.close();
            return maht + 1;
        } catch (Exception e) {
            e.printStackTrace();
            return 0;
        } finally {
            if (stmt != null) {
                try {
                    stmt.close();
                } catch (SQLException e) {
                }
            }
            if (rss != null) {
                try {
                    rss.close();
                } catch (SQLException e) {
                }
            }
            if (conn != null) {
                try {
                    conn.close();
                } catch (SQLException e) {
                }
            }
        }
    }

    public Vector LayDSNMA(int langid) {
        Vector v = new Vector();
        Connection conn = null;
        ResultSet rss = null;
        Statement stmt = null;
        try {
            ConnectionPool pool = new ConnectionPool();
            conn = pool.getConnection();
            stmt = conn.createStatement();
            String query = "select distinct(MaNMA), MaNN, MaLMA, TenNMA, TinhTrang from nhommonan where MaNN = " + langid + " Order by MaNMA desc ";
            rss = stmt.executeQuery(query);
            while (rss.next()) {
                Beans.NhomMA nma = new Beans.NhomMA();
                nma.setMaNMA(rss.getInt(1));
                nma.setMaNN(rss.getInt(2));
                nma.setMaLMA(rss.getInt(3));
                nma.setTenNMA(rss.getString(4));
                nma.setTinhTrang(rss.getByte(5));
                v.add(nma);
            }
            stmt.close();
            rss.close();
            conn.close();
        } catch (Exception e) {
            e.getMessage();
        } finally {
            if (stmt != null) {
                try {
                    stmt.close();
                } catch (SQLException e) {
                }
            }
            if (rss != null) {
                try {
                    rss.close();
                } catch (SQLException e) {
                }
            }
            if (conn != null) {
                try {
                    conn.close();
                } catch (SQLException e) {
                }
            }
        }
        return v;
    }

    public Vector LayDSNMAA(int langid) {
        Vector v = new Vector();
        Connection conn = null;
        ResultSet rss = null;
        Statement stmt = null;
        try {
            ConnectionPool pool = new ConnectionPool();
            conn = pool.getConnection();
            stmt = conn.createStatement();
            String query = "select distinct(MaNMA), MaNN, MaLMA, TenNMA, TinhTrang from nhommonan where MaNN = " + langid + " and TinhTrang = 1 Order by MaNMA desc ";
            rss = stmt.executeQuery(query);
            while (rss.next()) {
                Beans.NhomMA nma = new Beans.NhomMA();
                nma.setMaNMA(rss.getInt(1));
                nma.setMaNN(rss.getInt(2));
                nma.setMaLMA(rss.getInt(3));
                nma.setTenNMA(rss.getString(4));
                nma.setTinhTrang(rss.getByte(5));
                v.add(nma);
            }
            stmt.close();
            rss.close();
            conn.close();
        } catch (Exception e) {
            e.getMessage();
        } finally {
            if (stmt != null) {
                try {
                    stmt.close();
                } catch (SQLException e) {
                }
            }
            if (rss != null) {
                try {
                    rss.close();
                } catch (SQLException e) {
                }
            }
            if (conn != null) {
                try {
                    conn.close();
                } catch (SQLException e) {
                }
            }
        }
        return v;
    }

    public Vector LayDSNMATK(String manma, int langid) {
        Vector v = new Vector();
        Connection conn = null;
        ResultSet rss = null;
        Statement stmt = null;
        try {
            ConnectionPool pool = new ConnectionPool();
            conn = pool.getConnection();
            stmt = conn.createStatement();
            String query = "";
            if (manma.equals("all")) {
                query = "select distinct(MaNMA), MaNN, MaLMA, TenNMA, TinhTrang from nhommonan where MaNN = " + langid + " Order by MaNMA desc ";
            } else {
                query = "select distinct(MaNMA), MaNN, MaLMA, TenNMA, TinhTrang from nhommonan where MaNMA != " + manma + " and MaNN = " + langid + " Order by MaNMA desc ";
            }
            rss = stmt.executeQuery(query);
            while (rss.next()) {
                Beans.NhomMA nma = new Beans.NhomMA();
                nma.setMaNMA(rss.getInt(1));
                nma.setMaNN(rss.getInt(2));
                nma.setMaLMA(rss.getInt(3));
                nma.setTenNMA(rss.getString(4));
                nma.setTinhTrang(rss.getByte(5));
                v.add(nma);
            }
            stmt.close();
            rss.close();
            conn.close();
        } catch (Exception e) {
            e.getMessage();
        } finally {
            if (stmt != null) {
                try {
                    stmt.close();
                } catch (SQLException e) {
                }
            }
            if (rss != null) {
                try {
                    rss.close();
                } catch (SQLException e) {
                }
            }
            if (conn != null) {
                try {
                    conn.close();
                } catch (SQLException e) {
                }
            }
        }
        return v;
    }

    public static boolean AddNhomMA(int MaNMA, String maloaima, String nhomma) {
        Connection conn = null;

        Statement stmt = null;
        try {
            ConnectionPool pool = new ConnectionPool();
            conn = pool.getConnection();
            stmt = conn.createStatement();
            NgonNgu[] dsnn = NgonNgu_Model.DSNgonNgu();
            String x = "insert into nhommonan values ";
            for (int i = 0; i < dsnn.length; i++) {
                x = x + "(" + MaNMA + ", " + dsnn[i].getMaNN() + ", " + maloaima + " , '" + nhomma + "', 1),";
            }
            String query = x.substring(0, x.length() - 1);
            int n = stmt.executeUpdate(query);
            stmt.close();
            conn.close();
            if (n == 0) {
                return false;// thất bại
            }
            return true;
        } catch (Exception e) {
            e.printStackTrace();
            return false;
        } finally {
            if (stmt != null) {
                try {
                    stmt.close();
                } catch (SQLException e) {
                }
            }

            if (conn != null) {
                try {
                    conn.close();
                } catch (SQLException e) {
                }
            }
        }
    }

    public Vector LayFoodGroup(int MaNMA) {
        Connection conn = null;
        ResultSet rss = null;
        Statement stmt = null;
        Vector v = new Vector();
        try {
            ConnectionPool pool = new ConnectionPool();
            conn = pool.getConnection();
            stmt = conn.createStatement();
            String query = "select * from nhommonan where MaNMA = " + MaNMA;
            rss = stmt.executeQuery(query);
            while (rss.next()) {
                Beans.NhomMA nma = new Beans.NhomMA();
                nma.setMaNMA(rss.getInt(1));
                nma.setMaNN(rss.getInt(2));
                nma.setMaLMA(rss.getInt(3));
                nma.setTenNMA(rss.getString(4));
                nma.setTinhTrang(rss.getByte(5));
                v.add(nma);
            }
            stmt.close();
            rss.close();
            conn.close();
        } catch (Exception e) {
            e.getMessage();
        } finally {
            if (stmt != null) {
                try {
                    stmt.close();
                } catch (SQLException e) {
                }
            }
            if (rss != null) {
                try {
                    rss.close();
                } catch (SQLException e) {
                }
            }
            if (conn != null) {
                try {
                    conn.close();
                } catch (SQLException e) {
                }
            }
        }
        return v;
    }

    public static boolean Edit(String tennma, String manma, String mann) {
        Connection conn = null;

        Statement stmt = null;
        try {
            ConnectionPool pool = new ConnectionPool();
            conn = pool.getConnection();
            stmt = conn.createStatement();
            String query = "Update nhommonan set TenNMA = '" + tennma + "' where MaNMA = " + manma + " and MaNN = " + mann;
            int n = stmt.executeUpdate(query);
            stmt.close();
            conn.close();
            if (n == 0) {
                return false;// thất bại
            }
            return true;
        } catch (Exception e) {
            e.printStackTrace();
            return false;
        } finally {
            if (stmt != null) {
                try {
                    stmt.close();
                } catch (SQLException e) {
                }
            }

            if (conn != null) {
                try {
                    conn.close();
                } catch (SQLException e) {
                }
            }
        }
    }

    public static boolean UpdateStatusNMA(String manma, String status) {
        Connection conn = null;
        Statement stmt = null;
        try {
            ConnectionPool pool = new ConnectionPool();
            conn = pool.getConnection();
            stmt = conn.createStatement();
            String query = "Update nhommonan set TinhTrang = " + status + " where MaNMA = " + manma;
            int n = stmt.executeUpdate(query);
            stmt.close();
            conn.close();
            if (n == 0) {
                return false;// thất bại
            }
            return true;
        } catch (Exception e) {
            e.printStackTrace();

            return false;
        } finally {
            if (stmt != null) {
                try {
                    stmt.close();
                } catch (SQLException e) {
                }
            }

            if (conn != null) {
                try {
                    conn.close();
                } catch (SQLException e) {
                }
            }
        }
    }

     public static boolean UpdateLMA(String manma, String malma) {
        Connection conn = null;
        Statement stmt = null;
        try {
            ConnectionPool pool = new ConnectionPool();
            conn = pool.getConnection();
            stmt = conn.createStatement();
            String query = "Update nhommonan set MaLMA = " + malma + " where MaNMA = " + manma;
            int n = stmt.executeUpdate(query);
            stmt.close();
            conn.close();
            if (n == 0) {
                return false;// thất bại
            }
            return true;
        } catch (Exception e) {
            e.printStackTrace();

            return false;
        } finally {
            if (stmt != null) {
                try {
                    stmt.close();
                } catch (SQLException e) {
                }
            }

            if (conn != null) {
                try {
                    conn.close();
                } catch (SQLException e) {
                }
            }
        }
    }

    public static String LayTenLoaiMA(int malma, int langid) {
        Connection conn = null;
        ResultSet rss = null;
        Statement stmt = null;

        String maht = "";
        try {
            ConnectionPool pool = new ConnectionPool();
            conn = pool.getConnection();
            stmt = conn.createStatement();
            String query = "select TenLMA from loaimonan where MaNN = " + langid + " and MaLMA = " + malma;
            rss = stmt.executeQuery(query);
            while (rss.next()) {
                maht = rss.getString(1);
            }
            stmt.close();
            rss.close();
            conn.close();

        } catch (Exception e) {
            e.printStackTrace();
        } finally {
            if (stmt != null) {
                try {
                    stmt.close();
                } catch (SQLException e) {
                }
            }
            if (rss != null) {
                try {
                    rss.close();
                } catch (SQLException e) {
                }
            }
            if (conn != null) {
                try {
                    conn.close();
                } catch (SQLException e) {
                }
            }
        }
        return maht;
    }

    public Vector Search(String loaithucan, String tennhommonan, String status, int langid) {
        Connection conn = null;
        ResultSet rss = null;
        Statement stmt = null;
        Vector v = new Vector();
        try {
            ConnectionPool pool = new ConnectionPool();
            conn = pool.getConnection();
            stmt = conn.createStatement();
            String query = "";
            if (loaithucan.equals("") && tennhommonan.equals("") && status.equals("all")) {
                query = " select distinct(MaNMA), A.MaNN, A.MaLMA, TenNMA, TinhTrang from nhommonan A, loaimonan B where A.MaLMA = B.MaLMA and A.MaNN = " + langid + " "
                        + "   ";
            } else if (!loaithucan.equals("") && tennhommonan.equals("") && status.equals("all")) {
                query = " select distinct(MaNMA), A.MaNN, A.MaLMA, TenNMA, TinhTrang from nhommonan A, loaimonan B where A.MaLMA = B.MaLMA and A.MaNN = " + langid + " "
                        + " and  TenLMA like '%" + loaithucan + "%'  ";
            } else if (loaithucan.equals("") && !tennhommonan.equals("") && status.equals("all")) {
                query = " select distinct(MaNMA), A.MaNN, A.MaLMA, TenNMA, TinhTrang from nhommonan A, loaimonan B where A.MaLMA = B.MaLMA and A.MaNN = " + langid + " "
                        + " and  TenNMA like '%" + tennhommonan + "%' ";
            } else if (loaithucan.equals("") && tennhommonan.equals("") && !status.equals("all")) {
                query = " select distinct(MaNMA), A.MaNN, A.MaLMA, TenNMA, TinhTrang from nhommonan A, loaimonan B where A.MaLMA = B.MaLMA and A.MaNN = " + langid + " "
                        + " and TinhTrang = " + status + " ";
            } else if (!loaithucan.equals("") && !tennhommonan.equals("") && status.equals("all")) {
                query = " select distinct(MaNMA), A.MaNN, A.MaLMA, TenNMA, TinhTrang from nhommonan A, loaimonan B where A.MaLMA = B.MaLMA and A.MaNN = " + langid + " "
                        + " and TenLMA like '%" + loaithucan + "%' and TenNMA like '%" + tennhommonan + "%' ";
            } else if (!loaithucan.equals("") && tennhommonan.equals("") && !status.equals("all")) {
                query = " select distinct(MaNMA), A.MaNN, A.MaLMA, TenNMA, TinhTrang from nhommonan A, loaimonan B where A.MaLMA = B.MaLMA and A.MaNN = " + langid + " "
                        + "  and TenLMA like '%" + loaithucan + "%' and TinhTrang = " + status + " ";
            } else if (loaithucan.equals("") && !tennhommonan.equals("") && !status.equals("all")) {
                query = " select distinct(MaNMA), A.MaNN, A.MaLMA, TenNMA, TinhTrang from nhommonan A, loaimonan B where A.MaLMA = B.MaLMA and A.MaNN = " + langid + " "
                        + "  and TinhTrang = " + status + " and TenNMA like '%" + tennhommonan + "%' ";
            } else {
                query = " select distinct(MaNMA), A.MaNN, A.MaLMA, TenNMA, TinhTrang from nhommonan A, loaimonan B where A.MaLMA = B.MaLMA and A.MaNN = " + langid + " "
                        + " and TenNMA like '%" + tennhommonan + "%' and TenLMA like '%" + loaithucan + "%' and TinhTrang = " + status + "  ";
            }
            rss = stmt.executeQuery(query);
            while (rss.next()) {
                Beans.NhomMA nma = new Beans.NhomMA();
                nma.setMaNMA(rss.getInt(1));
                nma.setMaNN(rss.getInt(2));
                nma.setMaLMA(rss.getInt(3));
                nma.setTenNMA(rss.getString(4));
                nma.setTinhTrang(rss.getByte(5));
                v.add(nma);
            }
            stmt.close();
            rss.close();
            conn.close();
        } catch (Exception e) {
            e.getMessage();
        } finally {
            if (stmt != null) {
                try {
                    stmt.close();
                } catch (SQLException e) {
                }
            }
            if (rss != null) {
                try {
                    rss.close();
                } catch (SQLException e) {
                }
            }
            if (conn != null) {
                try {
                    conn.close();
                } catch (SQLException e) {
                }
            }
        }
        return v;
    }

    public Vector SortTenLMA(int langid, String order) {
        Connection conn = null;
        ResultSet rss = null;
        Statement stmt = null;
        Vector v = new Vector();
        try {
            ConnectionPool pool = new ConnectionPool();
            conn = pool.getConnection();
            stmt = conn.createStatement();
            String query = "select distinct(MaNMA), A.MaNN, A.MaLMA, TenNMA, TinhTrang from nhommonan A, loaimonan B where A.MaLMA = B.MaLMA and A.MaNN = " + langid + " Order by TenLMA " + order;
            rss = stmt.executeQuery(query);
            while (rss.next()) {
                Beans.NhomMA nma = new Beans.NhomMA();
                nma.setMaNMA(rss.getInt(1));
                nma.setMaNN(rss.getInt(2));
                nma.setMaLMA(rss.getInt(3));
                nma.setTenNMA(rss.getString(4));
                nma.setTinhTrang(rss.getByte(5));
                v.add(nma);
            }
            stmt.close();
            rss.close();
            conn.close();
        } catch (Exception e) {
            e.getMessage();
        } finally {
            if (stmt != null) {
                try {
                    stmt.close();
                } catch (SQLException e) {
                }
            }
            if (rss != null) {
                try {
                    rss.close();
                } catch (SQLException e) {
                }
            }
            if (conn != null) {
                try {
                    conn.close();
                } catch (SQLException e) {
                }
            }
        }
        return v;
    }

    public Vector SortTenNMA(int langid, String order) {
        Connection conn = null;
        ResultSet rss = null;
        Statement stmt = null;
        Vector v = new Vector();
        try {
            ConnectionPool pool = new ConnectionPool();
            conn = pool.getConnection();
            stmt = conn.createStatement();
            String query = "select * from nhommonan  where  MaNN = " + langid + " Order by TenNMA " + order;
            rss = stmt.executeQuery(query);
            while (rss.next()) {
                Beans.NhomMA nma = new Beans.NhomMA();
                nma.setMaNMA(rss.getInt(1));
                nma.setMaNN(rss.getInt(2));
                nma.setMaLMA(rss.getInt(3));
                nma.setTenNMA(rss.getString(4));
                nma.setTinhTrang(rss.getByte(5));
                v.add(nma);
            }
            stmt.close();
            rss.close();
            conn.close();
        } catch (Exception e) {
            e.getMessage();
        } finally {
            if (stmt != null) {
                try {
                    stmt.close();
                } catch (SQLException e) {
                }
            }
            if (rss != null) {
                try {
                    rss.close();
                } catch (SQLException e) {
                }
            }
            if (conn != null) {
                try {
                    conn.close();
                } catch (SQLException e) {
                }
            }
        }
        return v;
    }

    public Vector SortTTNMA(int langid, String order) {
        Connection conn = null;
        ResultSet rss = null;
        Statement stmt = null;
        Vector v = new Vector();
        try {
            ConnectionPool pool = new ConnectionPool();
            conn = pool.getConnection();
            stmt = conn.createStatement();
            String query = "select * from nhommonan  where  MaNN = " + langid + " Order by TinhTrang " + order;
            rss = stmt.executeQuery(query);
            while (rss.next()) {
                Beans.NhomMA nma = new Beans.NhomMA();
                nma.setMaNMA(rss.getInt(1));
                nma.setMaNN(rss.getInt(2));
                nma.setMaLMA(rss.getInt(3));
                nma.setTenNMA(rss.getString(4));
                nma.setTinhTrang(rss.getByte(5));
                v.add(nma);
            }
            stmt.close();
            rss.close();
            conn.close();
        } catch (Exception e) {
            e.getMessage();
        } finally {
            if (stmt != null) {
                try {
                    stmt.close();
                } catch (SQLException e) {
                }
            }
            if (rss != null) {
                try {
                    rss.close();
                } catch (SQLException e) {
                }
            }
            if (conn != null) {
                try {
                    conn.close();
                } catch (SQLException e) {
                }
            }
        }
        return v;
    }

    public Vector SortSearchTenLMA(int langid, String order, String loaithucan, String tennhommonan, String status) {
        Connection conn = null;
        ResultSet rss = null;
        Statement stmt = null;
        Vector v = new Vector();
        try {
            ConnectionPool pool = new ConnectionPool();
            conn = pool.getConnection();
            stmt = conn.createStatement();

            String query = "";
            if (loaithucan.equals("") && tennhommonan.equals("") && status.equals("all")) {
                query = " select distinct(MaNMA), A.MaNN, A.MaLMA, TenNMA, TinhTrang from nhommonan A, loaimonan B where A.MaLMA = B.MaLMA and A.MaNN = " + langid + " "
                        + " Order by TenLMA " + order;
            } else if (!loaithucan.equals("") && tennhommonan.equals("") && status.equals("all")) {
                query = " select distinct(MaNMA), A.MaNN, A.MaLMA, TenNMA, TinhTrang from nhommonan A, loaimonan B where A.MaLMA = B.MaLMA and A.MaNN = " + langid + " "
                        + " and  TenLMA like '%" + loaithucan + "%'  Order by TenLMA " + order;
            } else if (loaithucan.equals("") && !tennhommonan.equals("") && status.equals("all")) {
                query = " select distinct(MaNMA), A.MaNN, A.MaLMA, TenNMA, TinhTrang from nhommonan A, loaimonan B where A.MaLMA = B.MaLMA and A.MaNN = " + langid + " "
                        + " and  TenNMA like '%" + tennhommonan + "%' Order by TenLMA " + order;
            } else if (loaithucan.equals("") && tennhommonan.equals("") && !status.equals("all")) {
                query = " select distinct(MaNMA), A.MaNN, A.MaLMA, TenNMA, TinhTrang from nhommonan A, loaimonan B where A.MaLMA = B.MaLMA and A.MaNN = " + langid + " "
                        + " and TinhTrang = " + status + " Order by TenLMA " + order;
            } else if (!loaithucan.equals("") && !tennhommonan.equals("") && status.equals("all")) {
                query = " select distinct(MaNMA), A.MaNN, A.MaLMA, TenNMA, TinhTrang from nhommonan A, loaimonan B where A.MaLMA = B.MaLMA and A.MaNN = " + langid + " "
                        + " and TenLMA like '%" + loaithucan + "%' and TenNMA like '%" + tennhommonan + "%' Order by TenLMA " + order;
            } else if (!loaithucan.equals("") && tennhommonan.equals("") && !status.equals("all")) {
                query = " select distinct(MaNMA), A.MaNN, A.MaLMA, TenNMA, TinhTrang from nhommonan A, loaimonan B where A.MaLMA = B.MaLMA and A.MaNN = " + langid + " "
                        + "  and TenLMA like '%" + loaithucan + "%' and TinhTrang = " + status + " Order by TenLMA " + order;
            } else if (loaithucan.equals("") && !tennhommonan.equals("") && !status.equals("all")) {
                query = " select distinct(MaNMA), A.MaNN, A.MaLMA, TenNMA, TinhTrang from nhommonan A, loaimonan B where A.MaLMA = B.MaLMA and A.MaNN = " + langid + " "
                        + "  and TinhTrang = " + status + " and TenNMA like '%" + tennhommonan + "%' Order by TenLMA " + order;
            } else {
                query = " select distinct(MaNMA), A.MaNN, A.MaLMA, TenNMA, TinhTrang from nhommonan A, loaimonan B where A.MaLMA = B.MaLMA and A.MaNN = " + langid + " "
                        + " and TenNMA like '%" + tennhommonan + "%' and TenLMA like '%" + loaithucan + "%' and TinhTrang = " + status + "  Order by TenLMA " + order;
            }
            rss = stmt.executeQuery(query);
            while (rss.next()) {
                Beans.NhomMA nma = new Beans.NhomMA();
                nma.setMaNMA(rss.getInt(1));
                nma.setMaNN(rss.getInt(2));
                nma.setMaLMA(rss.getInt(3));
                nma.setTenNMA(rss.getString(4));
                nma.setTinhTrang(rss.getByte(5));
                v.add(nma);
            }
            stmt.close();
            rss.close();
            conn.close();
        } catch (Exception e) {
            e.getMessage();
        } finally {
            if (stmt != null) {
                try {
                    stmt.close();
                } catch (SQLException e) {
                }
            }
            if (rss != null) {
                try {
                    rss.close();
                } catch (SQLException e) {
                }
            }
            if (conn != null) {
                try {
                    conn.close();
                } catch (SQLException e) {
                }
            }
        }
        return v;
    }

    public Vector SortSearchTenNMA(int langid, String order, String loaithucan, String tennhommonan, String status) {
        Connection conn = null;
        ResultSet rss = null;
        Statement stmt = null;
        Vector v = new Vector();
        try {
            ConnectionPool pool = new ConnectionPool();
            conn = pool.getConnection();
            stmt = conn.createStatement();

            String query = "";
            if (loaithucan.equals("") && tennhommonan.equals("") && status.equals("all")) {
                query = " select distinct(MaNMA), A.MaNN, A.MaLMA, TenNMA, TinhTrang from nhommonan A, loaimonan B where A.MaLMA = B.MaLMA and A.MaNN = " + langid + " "
                        + " Order by TenNMA " + order;
            } else if (!loaithucan.equals("") && tennhommonan.equals("") && status.equals("all")) {
                query = " select distinct(MaNMA), A.MaNN, A.MaLMA, TenNMA, TinhTrang from nhommonan A, loaimonan B where A.MaLMA = B.MaLMA and A.MaNN = " + langid + " "
                        + " and  TenLMA like '%" + loaithucan + "%'  Order by TenNMA " + order;
            } else if (loaithucan.equals("") && !tennhommonan.equals("") && status.equals("all")) {
                query = " select distinct(MaNMA), A.MaNN, A.MaLMA, TenNMA, TinhTrang from nhommonan A, loaimonan B where A.MaLMA = B.MaLMA and A.MaNN = " + langid + " "
                        + " and  TenNMA like '%" + tennhommonan + "%' Order by TenNMA " + order;
            } else if (loaithucan.equals("") && tennhommonan.equals("") && !status.equals("all")) {
                query = " select distinct(MaNMA), A.MaNN, A.MaLMA, TenNMA, TinhTrang from nhommonan A, loaimonan B where A.MaLMA = B.MaLMA and A.MaNN = " + langid + " "
                        + " and TinhTrang = " + status + " Order by TenNMA " + order;
            } else if (!loaithucan.equals("") && !tennhommonan.equals("") && status.equals("all")) {
                query = " select distinct(MaNMA), A.MaNN, A.MaLMA, TenNMA, TinhTrang from nhommonan A, loaimonan B where A.MaLMA = B.MaLMA and A.MaNN = " + langid + " "
                        + " and TenLMA like '%" + loaithucan + "%' and TenNMA like '%" + tennhommonan + "%' Order by TenNMA " + order;
            } else if (!loaithucan.equals("") && tennhommonan.equals("") && !status.equals("all")) {
                query = " select distinct(MaNMA), A.MaNN, A.MaLMA, TenNMA, TinhTrang from nhommonan A, loaimonan B where A.MaLMA = B.MaLMA and A.MaNN = " + langid + " "
                        + "  and TenLMA like '%" + loaithucan + "%' and TinhTrang = " + status + " Order by TenNMA " + order;
            } else if (loaithucan.equals("") && !tennhommonan.equals("") && !status.equals("all")) {
                query = " select distinct(MaNMA), A.MaNN, A.MaLMA, TenNMA, TinhTrang from nhommonan A, loaimonan B where A.MaLMA = B.MaLMA and A.MaNN = " + langid + " "
                        + "  and TinhTrang = " + status + " and TenNMA like '%" + tennhommonan + "%' Order by TenNMA " + order;
            } else {
                query = " select distinct(MaNMA), A.MaNN, A.MaLMA, TenNMA, TinhTrang from nhommonan A, loaimonan B where A.MaLMA = B.MaLMA and A.MaNN = " + langid + " "
                        + " and TenNMA like '%" + tennhommonan + "%' and TenLMA like '%" + loaithucan + "%' and TinhTrang = " + status + "  Order by TenNMA " + order;
            }
            rss = stmt.executeQuery(query);
            while (rss.next()) {
                Beans.NhomMA nma = new Beans.NhomMA();
                nma.setMaNMA(rss.getInt(1));
                nma.setMaNN(rss.getInt(2));
                nma.setMaLMA(rss.getInt(3));
                nma.setTenNMA(rss.getString(4));
                nma.setTinhTrang(rss.getByte(5));
                v.add(nma);
            }
            stmt.close();
            rss.close();
            conn.close();
        } catch (Exception e) {
            e.getMessage();

        } finally {
            if (stmt != null) {
                try {
                    stmt.close();
                } catch (SQLException e) {
                }
            }
            if (rss != null) {
                try {
                    rss.close();
                } catch (SQLException e) {
                }
            }
            if (conn != null) {
                try {
                    conn.close();
                } catch (SQLException e) {
                }
            }
        }
        return v;
    }

    public Vector SortSearchTTNMA(int langid, String order, String loaithucan, String tennhommonan, String status) {
        Connection conn = null;
        ResultSet rss = null;
        Statement stmt = null;
        Vector v = new Vector();
        try {
            ConnectionPool pool = new ConnectionPool();
            conn = pool.getConnection();
            stmt = conn.createStatement();

            String query = "";
            if (loaithucan.equals("") && tennhommonan.equals("") && status.equals("all")) {
                query = " select distinct(MaNMA), A.MaNN, A.MaLMA, TenNMA, TinhTrang from nhommonan A, loaimonan B where A.MaLMA = B.MaLMA and A.MaNN = " + langid + " "
                        + " Order by TinhTrang " + order;
            } else if (!loaithucan.equals("") && tennhommonan.equals("") && status.equals("all")) {
                query = " select distinct(MaNMA), A.MaNN, A.MaLMA, TenNMA, TinhTrang from nhommonan A, loaimonan B where A.MaLMA = B.MaLMA and A.MaNN = " + langid + " "
                        + " and  TenLMA like '%" + loaithucan + "%'  Order by TinhTrang " + order;
            } else if (loaithucan.equals("") && !tennhommonan.equals("") && status.equals("all")) {
                query = " select distinct(MaNMA), A.MaNN, A.MaLMA, TenNMA, TinhTrang from nhommonan A, loaimonan B where A.MaLMA = B.MaLMA and A.MaNN = " + langid + " "
                        + " and  TenNMA like '%" + tennhommonan + "%' Order by TinhTrang " + order;
            } else if (loaithucan.equals("") && tennhommonan.equals("") && !status.equals("all")) {
                query = " select distinct(MaNMA), A.MaNN, A.MaLMA, TenNMA, TinhTrang from nhommonan A, loaimonan B where A.MaLMA = B.MaLMA and A.MaNN = " + langid + " "
                        + " and TinhTrang = " + status + " Order by TinhTrang " + order;
            } else if (!loaithucan.equals("") && !tennhommonan.equals("") && status.equals("all")) {
                query = " select distinct(MaNMA), A.MaNN, A.MaLMA, TenNMA, TinhTrang from nhommonan A, loaimonan B where A.MaLMA = B.MaLMA and A.MaNN = " + langid + " "
                        + " and TenLMA like '%" + loaithucan + "%' and TenNMA like '%" + tennhommonan + "%' Order by TinhTrang " + order;
            } else if (!loaithucan.equals("") && tennhommonan.equals("") && !status.equals("all")) {
                query = " select distinct(MaNMA), A.MaNN, A.MaLMA, TenNMA, TinhTrang from nhommonan A, loaimonan B where A.MaLMA = B.MaLMA and A.MaNN = " + langid + " "
                        + "  and TenLMA like '%" + loaithucan + "%' and TinhTrang = " + status + " Order by TinhTrang " + order;
            } else if (loaithucan.equals("") && !tennhommonan.equals("") && !status.equals("all")) {
                query = " select distinct(MaNMA), A.MaNN, A.MaLMA, TenNMA, TinhTrang from nhommonan A, loaimonan B where A.MaLMA = B.MaLMA and A.MaNN = " + langid + " "
                        + "  and TinhTrang = " + status + " and TenNMA like '%" + tennhommonan + "%' Order by TinhTrang " + order;
            } else {
                query = " select distinct(MaNMA), A.MaNN, A.MaLMA, TenNMA, TinhTrang from nhommonan A, loaimonan B where A.MaLMA = B.MaLMA and A.MaNN = " + langid + " "
                        + " and TenNMA like '%" + tennhommonan + "%' and TenLMA like '%" + loaithucan + "%' and TinhTrang = " + status + "  Order by TinhTrang " + order;
            }
            rss = stmt.executeQuery(query);
            while (rss.next()) {
                Beans.NhomMA nma = new Beans.NhomMA();
                nma.setMaNMA(rss.getInt(1));
                nma.setMaNN(rss.getInt(2));
                nma.setMaLMA(rss.getInt(3));
                nma.setTenNMA(rss.getString(4));
                nma.setTinhTrang(rss.getByte(5));
                v.add(nma);
            }
            stmt.close();
            rss.close();
            conn.close();
        } catch (Exception e) {
            e.getMessage();

        } finally {
            if (stmt != null) {
                try {
                    stmt.close();
                } catch (SQLException e) {
                }
            }
            if (rss != null) {
                try {
                    rss.close();
                } catch (SQLException e) {
                }
            }
            if (conn != null) {
                try {
                    conn.close();
                } catch (SQLException e) {
                }
            }
        }
        return v;
    }

    public static String LayTenNhomMA(int manma, int langid) {
        Connection conn = null;
        ResultSet rss = null;
        Statement stmt = null;

        String maht = "";
        try {
            ConnectionPool pool = new ConnectionPool();
            conn = pool.getConnection();
            stmt = conn.createStatement();
            String query = "select TenNMA from nhommonan where MaNN = " + langid + " and MaNMA = " + manma;
            rss = stmt.executeQuery(query);
            while (rss.next()) {
                maht = rss.getString(1);
            }
            stmt.close();
            rss.close();
            conn.close();

        } catch (Exception e) {
            e.printStackTrace();
        } finally {
            if (stmt != null) {
                try {
                    stmt.close();
                } catch (SQLException e) {
                }
            }
            if (rss != null) {
                try {
                    rss.close();
                } catch (SQLException e) {
                }
            }
            if (conn != null) {
                try {
                    conn.close();
                } catch (SQLException e) {
                }
            }
        }
        return maht;
    }

    public static boolean CheckTenNMA(String nhomma, int langid) {
        Connection conn = null;
        ResultSet rss = null;
        Statement stmt = null;
        try {
            ConnectionPool pool = new ConnectionPool();
            conn = pool.getConnection();
            stmt = conn.createStatement();
            int n = 0;
            String query = "select * from nhommonan where MaNN = " + langid + " and TenNMA = '" + nhomma + "' ";
            rss = stmt.executeQuery(query);
            while (rss.next()) {
                n = n + 1;
            }
            stmt.close();
            rss.close();
            conn.close();
            if (n == 0) {
                return false;// thất bại
            }
            return true;
        } catch (Exception e) {
            e.printStackTrace();
            return false;
        } finally {
            if (stmt != null) {
                try {
                    stmt.close();
                } catch (SQLException e) {
                }
            }
            if (rss != null) {
                try {
                    rss.close();
                } catch (SQLException e) {
                }
            }
            if (conn != null) {
                try {
                    conn.close();
                } catch (SQLException e) {
                }
            }
        }
    }

    public static boolean CheckTenNMAEdit(String manma, String nhomma, int langid) {
        Connection conn = null;
        ResultSet rss = null;
        Statement stmt = null;
        try {
            ConnectionPool pool = new ConnectionPool();
            conn = pool.getConnection();
            stmt = conn.createStatement();
            int n = 0;
            String query = "select * from nhommonan where MaNN = " + langid + " and TenNMA = '" + nhomma + "' and MaNMA != " + manma + " ";
            rss = stmt.executeQuery(query);
            while (rss.next()) {
                n = n + 1;
            }
            stmt.close();
            rss.close();
            conn.close();
            if (n == 0) {
                return false;// thất bại
            }
            return true;
        } catch (Exception e) {
            e.printStackTrace();
            return false;
        } finally {
            if (stmt != null) {
                try {
                    stmt.close();
                } catch (SQLException e) {
                }
            }
            if (rss != null) {
                try {
                    rss.close();
                } catch (SQLException e) {
                }
            }
            if (conn != null) {
                try {
                    conn.close();
                } catch (SQLException e) {
                }
            }
        }
    }

    public Vector DanhSachNMA(int manma, int langid) {
        Connection conn = null;
        ResultSet rss = null;
        Statement stmt = null;
        Vector v = new Vector();
        try {
            ConnectionPool pool = new ConnectionPool();
            conn = pool.getConnection();
            stmt = conn.createStatement();
            String query = "select * from nhommonan where MaNMA != " + manma + " and MaNN = " + langid;
            rss = stmt.executeQuery(query);
            while (rss.next()) {
                Beans.NhomMA nma = new Beans.NhomMA();
                nma.setMaNMA(rss.getInt(1));
                nma.setMaNN(rss.getInt(2));
                nma.setMaLMA(rss.getInt(3));
                nma.setTenNMA(rss.getString(4));
                nma.setTinhTrang(rss.getByte(5));
                v.add(nma);
            }
            stmt.close();
            rss.close();
            conn.close();
        } catch (Exception e) {
            e.getMessage();
        } finally {
            if (stmt != null) {
                try {
                    stmt.close();
                } catch (SQLException e) {
                }
            }
            if (rss != null) {
                try {
                    rss.close();
                } catch (SQLException e) {
                }
            }
            if (conn != null) {
                try {
                    conn.close();
                } catch (SQLException e) {
                }
            }
        }
        return v;
    }

    public static int LayMaNMA() {
        Connection conn = null;
        ResultSet rss = null;
        Statement stmt = null;
        int manma = 0;
        try {
            ConnectionPool pool = new ConnectionPool();
            conn = pool.getConnection();
            stmt = conn.createStatement();
            String query = "select max(MaNMA) from nhommonan ";
            rss = stmt.executeQuery(query);
            while (rss.next()) {
                manma = rss.getInt(1);
            }
            stmt.close();
            rss.close();
            conn.close();

        } catch (Exception e) {
            e.printStackTrace();
        } finally {
            if (stmt != null) {
                try {
                    stmt.close();
                } catch (SQLException e) {
                }
            }
            if (rss != null) {
                try {
                    rss.close();
                } catch (SQLException e) {
                }
            }
            if (conn != null) {
                try {
                    conn.close();
                } catch (SQLException e) {
                }
            }
        }
        return manma;
    }
    public static NhomMA[] DSNMA(int langid) {

        Vector vdstso = new Vector();
        String query = "";
        Connection conn = null;
        ResultSet rss = null;
        Statement stmt = null;
        try {
            ConnectionPool pool = new ConnectionPool();
            conn = pool.getConnection();
            stmt = conn.createStatement();
            query = " select A.MaNMA, TenNMA, sum(B.ThanhTien) " +
                    " from nhommonan A, chitiethd B, monan C " +
                    " where A.MaNN = 2 and A.MaNMA = C.MaNMA and B.MaMA = C.MaMA " +
                    " Group by A.MaNMA " +
                    " Order by sum(B.ThanhTien) desc" +
                    " limit 9 ";
            rss = stmt.executeQuery(query);
            while (rss.next()) {
                NhomMA nma = new NhomMA();
                nma.setMaNMA(rss.getInt(1));                
                nma.setTenNMA(rss.getString(2));
                vdstso.add(nma);
            }
            stmt.close();
            rss.close();
            conn.close();
            return convertToArray(vdstso);

        } catch (Exception e) {
            return null;
        }
        finally {
            if (stmt != null) {
                try {
                    stmt.close();
                } catch (SQLException e) {
                }
            }
            if (rss != null) {
                try {
                    rss.close();
                } catch (SQLException e) {
                }
            }
            if (conn != null) {
                try {
                    conn.close();
                } catch (SQLException e) {
                }
            }
        }
    }
    public static NhomMA[] convertToArray(Vector v) {
        int n = v.size();
        NhomMA[] arr = new NhomMA[n];
        for (int i = 0; i < n; i++) {
            arr[i] = (NhomMA) v.get(i);
        }
        return arr;
    }
}
